﻿<%@ page language="java" import="java.sql.*" contentType="text/html;charset=utf-8" %>

<HTML>
<HEAD>
<TITLE>▒▒ 게시판 ▒▒</TITLE>

<style type="text/css">
<!--
  .table {font-size:9pt; font-family : 돋움 ;font-weight:bold;color:white;background:#6699CC; }
a {text-decoration: none; }
  td	{ font-size : 10pt ; font-family : 돋움 ; font-decoration : none ;}
  font	{ font-size : 10pt ; font-family : 돋움 ; font-decoration : none ; }
  #title{ font-size : 10pt ; font-family : 돋움 ; font-decoration : none ; font-weight : bold ; color : ffffff ;}
 .dform { font-family: 돋움 ; font-size:10pt;color:white;background:#6699CC;}
 .ldTable { border-bottom:#636363 solid 1px;border-color:white; padding-top:5px; padding-bottom:5px; padding-left:3px;
            padding-right:3px; font-size:9pt}
-->
</style>

</HEAD>
<BODY bgcolor="#FFFFFF">

<CENTER>
<FONT size=2><B> 게시판 글목록 </B></FONT>
</CENTER>
<HR>

<p align="center"><br>
<div align="center">
<table border="0" cellpadding="0" cellspacing="0" width="600">
    <tr>
        <td bgcolor="#FFFFFF">
        
        <table border="0" cellpadding="2" cellspacing="1" width="600">
        	<tr>
            	<th width="8%" align="center" bgcolor="#8EA2FF">
            		<p><font size="2" color=white>번호</font>
            	</th>            		
                <th width="48%" align="center" bgcolor="#8EA2FF">
                	<p><font size="2" color=white>제 목</font>
                </th>
                <th width="15%" align="center" bgcolor="#8EA2FF">
                	<p><font size="2" color=white>글쓴이</font>
                </th>
                <th width="15%" align="center" bgcolor="#8EA2FF">
                	<p><font size="2" color=white>날짜</font>
                </th>
                <th width="8%" align="center" bgcolor="#8EA2FF">
                	<p><font size="2" color=white>조회</font>
                </th>
            </tr>

<%	
	String DB_URL = "jdbc:mysql://localhost:3306/bbs";
	String DB_USER = "root";
	String DB_PASSWORD = "6593";

	Connection conn = null;
	Statement stmt1 = null,stmt2=null;
	ResultSet result1 = null,result2=null; 
	
	String query1 = "SELECT max(IDX) FROM GUEST";
	String query2 = "";

	String sName,sEmail,sTitle,sContent,sMoment,sPwd,sTo; //각 태그의 값을 셋팅하기위한 변수

	int idx=0, hit=0, totalCount=0; // 클릭 수, 전체 글 갯수
	int pageCount=0, remainCount=0; // 페이지 수
	int firstRow=0, lastRow=0, bFirstRow=0; //첫글과 마지막 글 저장변수
	int bLastRow=0, nFirstRow=0, nLastRow=0;

	Class.forName("com.mysql.jdbc.Driver");
	conn = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);
	stmt1=conn.createStatement();	

	result1 = stmt1.executeQuery(query1);

	if(result1.next()){
		totalCount=result1.getInt(1);
		result1.close();
		stmt1.close();
	}

	if(request.getParameter("firstRow")!=null){
		firstRow=Integer.parseInt(request.getParameter("firstRow"));
		lastRow=Integer.parseInt(request.getParameter("lastRow"));

		pageCount=totalCount/10; //총 페이지 수
		remainCount=totalCount%10; //마지막 페이지 글수

		if(totalCount < 10){
			firstRow=1;
			lastRow=remainCount;
		}
	}else { //아무 변수도 안넘어올때
		firstRow = totalCount-9;
		lastRow = totalCount;
	}
	//--------여기까지 총 레코드 갯수, 페이지수
	//--------시작 페이지의 첫번쨰 글번호 ,마지막 글번호 추출

	stmt2 = conn.createStatement();
	query2="SELECT IDX,NAME,EMAIL,TITLE,CONTENT,date_format(MOMENT,'%Y-%m-%e'),PASSWORD,HIT FROM GUEST WHERE IDX >= "+firstRow+" AND IDX <= "+lastRow+" ORDER BY IDX DESC";
	result2 = stmt2.executeQuery(query2);

	while(result2.next()) {
		idx=result2.getInt(1);
		sName=result2.getString(2);
		sEmail=result2.getString(3);
		sTitle=result2.getString(4);
		sContent=result2.getString(5);
		sMoment=result2.getString(6);
		sPwd=result2.getString(7);
		hit=result2.getInt(8);
		// 이메일은 필수사항이 아니기 떄문에...
		if(!sEmail.equals("")){
			sTo="<a href=mailto;"+sEmail+">"+sName+"</a>";
		}else{
			sTo=sName;
		}

		 
%>
                
                <tr>
                    <td align="center" bgcolor="#F6F6F6">
                    	<p><font size="2"><%=idx%></font>
                    </td>
                    <td width="364" align="left" bgcolor="#F6F6F6">                    	
                    	&nbsp;&nbsp;
                    	<a href="view.jsp?idx=<%=idx%>">
                    	<font size="2"><%=sTitle%></font>
                    	</a>
                    </td>
                    <td width="94" align="center" bgcolor="#F6F6F6"><p><font
                         size="2"><%=sTo%></font></td>
                    <td width="114" align="center" bgcolor="#F6F6F6"><p><font
                         size="2"><%=sMoment%></font></td>
                    <td align="center" bgcolor="#F6F6F6"><p><font size="2"><%=hit%></font></td>
                </tr>
<%	
	} //while() end
	stmt2.close();
	conn.close();  
%>
            </table></td>
    </tr>

</table>
	
</div>

<br>

<p align="center">
  <table>
	  <tr>
	  	<td>
<%
			if(lastRow==totalCount) {
%>
				<img src="image/prev.gif" border="0">
<%
			} else {	//이전 버튼 클릭시
				bFirstRow=lastRow+1;
				bLastRow=bFirstRow+9;
				if(bLastRow>totalCount)
					bLastRow=totalCount; //마지막 글번호 값이 전체 값과 같다 
%>
				<a href=list.jsp?firstRow=<%=bFirstRow%>&lastRow=<%=bLastRow%>><img src="image/prev.gif" border="0"></a><!-- 여기 -->
<%
			}
%>	  		
	  	</td>
	  	<td>&nbsp;&nbsp;</td>
	  	<td>
	  		<a href="write.jsp"><img src="image/accept.gif" border="0"></a>	  <!-- 여기 -->		
	  	</td>
	  	<td>&nbsp;&nbsp;</td>
	  	<td>
<%
			if(firstRow==1){
%>
		<img src="image/next.gif" border="0">
<%
			} else {	//다음 버튼 클릭시
				nFirstRow=firstRow-10;
				nLastRow=nFirstRow+9;
				if(nFirstRow < 1)
					nFirstRow=1;
 
%>	
			<a href=list.jsp?firstRow=<%=nFirstRow%>&lastRow=<%=nLastRow%>><img src="image/next.gif" border="0"></a><!-- 여기 -->
<%
			}
%>	  		
	  	</td>	  	
	  </tr>
  </table>
  </p>

</BODY>

</HTML>
